Best Practice - iLoader - Strategies For Loading Data

By way of a refresh to existing administrators, this section runs through a few behaviors that are now default for Engine. Existing scripts that have not been updated for a few releases may well be performing processes that are now redundant and removing them will reduce load time and therefore downtime for the system. Familiarity with these behaviors is key for on-going load optimizations.

Incremental Linking

Functionality implemented for Incremental Linking in Engine 5.1 means that if you Append or Delete data from your database, you no longer have to drop all the links before the Append/Delete or re-create them afterwards. Engine will re-calculate only those links that are required for the new data. It is highly recommended that scripts are edited to remove drop link statements in order to benefit from the performance improvements of this change.

Existing scripts that include a drop link statement will continue to work, however, improved performance can be achieved by not dropping links unnecessarily.

External Indexing

External Indexing is now switched on by default. This is the process of performing link processing within a NucleusSurrogate.exe process and not the standard NucEngine.exe

As link are high CPU usage, having them executed in an external surrogate processes removes some of the processing load on the NucEngine.exe. It should be notes that this is an area where an under spec’d machine could cause CPU contention so this should be carefully monitored.

Exclusivity

Exclusivity is no longer controllable by the user in Engine 7.0.0. It is handled automatically by Engine via the new locking mechanism introduced in Engine 7.0.0.

Enhanced Security

This setting is switched on by default, and can be switched off if required

Understanding the object dependency model from Engine 7.x with Campaign Manager 5.1

Prior to this, the relationship between database objects was based on their NameEx i.e. You would create an ‘Age Band’ decode column that uses the NameEx [Demo].[Customer].[Age] to find the source column.

To ensure data integrity as part of the locking improvements introduced in Engine 7.0 with CM 5.1, the relationship between database objects is now entrenched by a unique, automatically allocated ID, and not their NameEx. Additionally, all engineered objects now track the specific link path used when they were created.

This means that if you refer to an object in an engineered field, and then delete the object and recreate it with the same name, then the reference will be lost as the object will have a new unique identifier and the expression will be invalidated. The same invalidation will also occur if you delete a link that the expression is traversing to get to its object.

Example 1

Pre-Engine 7.0.0 behavior

• User creates an expression EXP1 using this syntax “[Demo].[Customer].[Age] + 5”.
• The EXP1 column is balanced and shows results – it uses the NameEx [Demo].[Customer].[Age] to find the source data.
• User deletes the [Demo].[Customer].[Age] column.
• The EXP1 column is invalid and will show no data.
• The user then manually creates another column called [Demo].[Customer].[Age] but this is not the same column as the first one – maybe it is the age of the account – i.e. how many years the person has been a customer.
• The EXP1 column finds the new column using its NameEx - ([Demo].[Customer].[Age]) and again shows data – even though it is not valid to do so.

Engine 7.x behavior

• User creates an expression EXP2 using this syntax “[Demo].[Customer].[Age] + 5”.
• The EXP2 column is balanced and shows results – it uses the unique identifier allocated by engine to find the source data.
• User deletes the [Demo].[Customer].[Age] column.
• The EXP2 column is invalid and will show no data.
• The user then manually recreates the [Demo].[Customer].[Age] column.
• The EXP2 column is still invalid and will show no data because the recreated column called [Demo].[Customer].[Age] has been given a new unique identifier by Engine.

Example 2

Pre-Engine 7.0.0 behavior

• User creates an expression EXP3 using this syntax “[Demo].[Customer].[Age] + [Demo].[Order].[Order Lines]” this traverses the link between the Customer and Order tables.
• The EXP3 column is balanced and shows results it uses the NameEx of the link to find the source data.
• User deletes the link between the Customer and Order tables.
• The EXP3 column is invalid and will show no data.
• The user then manually recreates the link between the Customer and Order tables.
• The EXP3 column finds the link by NameEx and again shows data.

Engine 7.x behavior

• User creates an expression EXP4 using this syntax “[Demo].[Customer].[Age] + [Demo].[Order].[Order Lines]” this traverses the link between the Customer and Order tables.
• The EXP4 column is balanced and shows results it uses the unique identifier of the link to find the source data.
• User deletes the link between the Customer and Order tables.
• The EXP4 column is invalid and will show no data.
• The user then manually recreates the link between the Customer and Order tables.
• The EXP4 column is still invalid and will show no data because the new link has been given a new unique identifier by Engine.

Renaming Tables and Columns

This is possible via iLoader but you must be aware of the dependency model, i.e. referring to Example 1 above, if you change a column called [Age] to [AgeOld] and create a new column called [Age] then any related objects will still be linked to [AgeOld] via the object’s unique identifier.

Starting iLoader

it is recommended initially that iLoader is started via AMC and not via any default Start menu items as these are not Engine project aware.

Logging and Audit

At the start of any new load process, it is recommended that a new Cerlog is started. This is to prevent confusion and makes it easier to identify issues.

Use the START_NEW_ENGINE_LOG command to do this.

If possible, we recommend including logging information to your script to be used for data audit purposes. There are two ways of doing this:

The PUBLISH command creates a text or tab based Data Audit. Refer to the iLoader Help File for further details.

The DATA AUDIT command produces a file containing information about the database. For performance reasons, you can now exclude the statistical portion of the audit file by adding the parameter NOSTATS:

DATA_AUDIT NOSTATS (no statistics shown in audit)

DATA_AUDIT STATS (statistics will be shown)

Stats are switched on by default and old DATA_AUDIT commands with no switch will default to the show stats method.

When loading data into Engine or backing up the repository, it is a key requirement that the Project state is maintained for the duration of the load and Engine does not start up, either automatically, or because a user attempts to log into a client application. In an enterprise environment with multiple client applications and geographically distributed users, this becomes a difficult process to manage for administrators.

Exclusive Maintenance Mode functionality enables administrators to put the Engine Project into an exclusive 'single user' state while administrative tasks are completed.

This functionality includes several iLoader commands and NucBroker features.

How it Works

1. Switch the Project to RESTRICTED using a secret token. This operation MAY fail if the Project is already locked, so check the return result from the “SetProjectState” call.
2. Wait for the server to stop. If it does not stop by itself within a reasonable length of time then it may be necessary to use “DisconnectEngineProcess”.
3. Perform the required single-user tasks. Nobody else will be able to connect without knowing the secret token.
4. Switch the Project back to ACTIVE using the secret token. It is not actually necessary to wait for the server process to finish before doing this, as the running process will remain locked.

Example iLoader Script

;; Check the initial Project state, exit scripts if server crashes (PreLoad script file)

IF_PROJECT_IN_RECOVERY_STOP

;; Set the access token, this is optional, as a default value exists.

;; Do not alter token value if Project has been changed to inactive or restricted states!

SET_ACCESS_TOKEN secret_token

;; Optional clean up. This used to be done in the control file (with the optional ENABLE_CLEANUP=True).

;; It’s now been moved to separate and controllable commands

SET_PROJECT_MODE_ACTIVE

REMOVE_TEMP_FILES

REMOVE_BAD_LINKS

CLEANUP_DATABASE

;; Now disallow any RESTARTS of Engine. This does not stop the server if it is running

SET_PROJECT_MODE_INACTIVE

;; Wait up to 1 minute for the server to shut-down on its own (if it is running)

WAIT_FOR_SERVER_STOP 60

;; if it is still running then shut down by disconnecting COM

;; NOTE: server cannot restart as Project in now INACTIVE

 

IF_NOT_SERVER_STOPPED

RECYCLEENGINE 1,1

WAIT_FOR_SERVER_STOP 120

IF_NOT_SERVER_STOPPED

STOP

ENDIF

ENDIF

;; the server is stopped and WILL NOT restart, alter the file system in safety

;; switch to restricted LOAD mode, the secret token prevents other users from starting / using Engine

 

SET_PROJECT_MODE_RESTRICTED

;; the server is in restricted mode for normal loader commands

;; Loading complete. Optionally, force an Engine stop

WAIT_FOR_SERVER_STOP 120

IF_NOT_SERVER_STOPPED

RECYCLEENGINE 1,1,false

WAIT_FOR_SERVER_STOP 120

IF_NOT_SERVER_STOPPED

SET_PROJECT_MODE_INACTIVE

STOP

ENDIF

ENDIF

;; make the project available to all (PostLoad script file)

SET_PROJECT_MODE_ACTIVE

When we consider a column to be loaded, we will initially define a Data Type, i.e. UNICODE, TEXT, INTEGER, REA etc, but also important are other properties like cardinality (number of discrete values), max width and the scope of usage of that column within the database.

Cardinality

What is the maximum number of discrete values the column is likely to have over its life cycle. This defines the underlying Engine storage and is absolutely key. Cardinality is initially classified as:

• Low Cardinality - < 32,000 discrete values
• High Cardinality > 32,000 discrete values

Scope of Usage

How a column is used will directly affect decision to index, which is covered later in the index strategy section but administrators would need to know:

• Will the column be regularly queried?
• Will the column be used as a dimension in a crosstabulation?
• Will the column be part of channel output?

Width

The defined width of a column determines its disk footprint and under certain circumstances its storage when considering MEMO fields. It would also govern the width of a fixed width export.

Overview

As discussed above, part of database design is to now information about the data to be loaded and for each column in the database and indexation is largely focused on how it will most likely be used. These decisions are key to achieve the optimal performance of the data both in reducing load times and therefore system down time, by only indexing those column that need it, as well as performance by ensuring that Engine has the indexes available to give the maximum response times to queries and is stored optimally for size on disk. more than ever, these decisions are important within an Engine repository.

Carrying out unnecessary indexing will:

• Increase Load Time.
• Increase Disk Footprint.
• Add no value to the user.

It is certainly true that in this area, the administrator can impact on storage and performance, and taking time to learn options and apply the correct one, can reap rewards later down the line.

As data is loaded, the default option is to load all fields indexed. This is achieved simply by declaring the column as TEXT, INTEGER etc. To load a column un-indexed simply add a “U” to this declaration i.e. UTEXT, UINTEGER.

The following should be considered when looking at column indexes on tables:

• Campaign Keys must be indexed.
• Primary link keys must be indexed.
• For reporting, High Cardinality fields (>32,000 discrete values) of any type, that will be used in a crosstab, must be indexed or the crosstab will fail.
• For querying, High Cardinality fields (>32,000 discrete values) of any data type, should only be indexed where is it necessary to see values in the helpers or columns that will be regularly queried.
• Low Cardinality (< 32,000 discrete values) columns do not need indexing for crosstabs, querying or value visibility. Query performance will improve query speed but the overhead of indexing may be considered more impacting.

Dropping and Rebuilding Indexes

There is no longer any need to do this, especially when doing Appends.

Parallel Indexing

For some customers, indexing takes up a large proportion of an overall load process. If this is the case then it may be useful to run multiple indexes in parallel. When considering this option, consider the following:

• Does the Engine server have enough memory to fit more than one index job in memory?
• Does the Engine server have sufficient number of cores to perform more than one index job?
• Will the data storage device be able to provide the necessary I/O without experiencing disk contention?

Assuming the answer to all the above is yes parallel indexing may offer significant time savings.

Implementation Options

There are two ways of achieving parallel indexing:

1. By default, if you haven’t dropped your indexes before loading data then Engine will re-index the columns in parallel on data load. The degree of parallelism is controlled by the Engine External Process Configuration Applet in AMC (default is 2).
2. Deliberately add the CREATEINDEXES command to an iLoader script so that you can have control over which indexes are created in parallel, and the order.

Furthermore, there are other decisions to be made depending on the infrastructure available:

• Distributing index jobs to additional servers.
• Make use of Parallel Loading capabilities.

The decision on whether to make use of a Support Server and implement distributed processing is only an issue if a support server is available and if it has good (by which we mean fast network) access to the Repository.

Assuming it does, and assuming the Support Server is of comparable specification to the Engine server, it should prove more optimal to make use of the support server as there will be less competition between the index processes for machine resource.

If the decision has been taken to run multiple concurrent indexes, there are then two possible options:

1. Make use of the CREATEINDEXES command in an iLoader Script.
2. Run multiple copies of iLoader and index tables in parallel.

How many Parallel Indexes can I have?

There is no straightforward answer to this unfortunately – it depends on hardware and the individual environment. There will be a point at which adding additional instances will have no benefit, and this point can only be found through trial and error on your own environment.

By default, the Engine server is configured to run a maximum of two instances. This value can be modified by using the Engine External Process Configuration Applet in AMC.

We recommend that this number is changed gradually and you do not start by ramping it up significantly.

Additional servers can also have Max Instances controlled on a server-by-server basis.

Switching on Parallel Indexing

This is turned on by default.

Engineering

To make use of parallel indexing when creating engineering fields for the first time on a brand new table, create the fields as UNINDEXED.

It is possible to create EXPRESSION fields that are un-indexed by using UEXPRESSION instead of EXPRESSION.

UEXPRESSION destination_tbNameEx, fdName, {opt - Format}, {Expression Clause}

It is also possible to create un-indexed AGGREGATE fields.

UAGGREGATE, Full Table Name, Short Name for Field, Type of Aggregate, Full Target Field Name, {Full Alterian Engine SQL of filter to apply or blank}.

Then use CREATEINDEXES to index the fields.

Partial Indexing

When appending to an existing table, where possible Engine will only index the new rows rather than re-indexing the entire table, which was necessary in older versions.

Overview

Data Type selection is mostly a clear decision based on the understanding of the raw data, certainly to the basics of TEXT, INTEGER, BIGINT, REAL, DATE, TIME and DATETIME, but with this release, there are further options for loading textural data. So strictly speaking TEXT is used for single byte data sets and UNICODE for double-byte data sets, but a new UNICODE storage type has characteristics that could be useful for single-byte data and in some cases the choice of UNICODE could be the best choice.

To better understand this, it is important to understand how Engine column storage works

Data Types have different storage based on their cardinality, and each storage has different characteristics, so knowledge in this area may also help drive choice of Data Type. It is important to understand that Engine decides storage based on user defined Data Type so it is the decision of Data Type that is important.

There are 3 different storages in Engine, called DISCRETE, GENERIC and VARUNICODE and the following table shows how these are implemented.

Having looked at the storage that is employed by Engine, the following table shows the key characteristics. It should be noted that these are general trends but as this many any configurable options, experimentation is recommended on particular data shapes.

Storage Summary

• If your TEXT data will always be Low Card and will never contain Double-byte characters, load as TEXT.
• They are faster to load than their Low Card UNICODE equivalent.
• They have less size on disk as stored single-byte and the low card UNICODE are not compressed.
• If your TEXT data is High Cardinality and wide, i.e. would go MEMO (by default > 64 characters) load as UNICODE rather than TEXT regardless of whether it has Double-byte data.
• High Cardinality VARUNICODE storage columns can be up to 60% smaller on disk compared to Memo.
• High Cardinality VARUNICODE storage columns are faster to load compared to Memo.
• High Cardinality VARUNICODE storage columns are faster to query compared to Memo.

The four very key pieces of information are

• Does the data or will the data in the future contain double-byte characters?
• What is the maximum number of discrete values the column is likely to have over its life cycle. This defines the underlying Engine and is absolutely key, so will be defined as:
• Low Cardinality (< 32,00 discrete values)
• High Cardinality (> 32,000 discrete values)
• Will the column be regularly queried?
• Will the column be used as a dimension in a crosstabulation?
• Will the column be part of channel output?

When considering an Intra-Day update strategy, please see See Campaign Manager - Best Practice Guides.

This document uses the following terminology when referring to the different methods of updating Engine data. Further detail on each function is included in the body of the document.

Append: This refers to the process of adding new records to an existing table; the records will be appended to the end of the table.

Update: This refers to the process of updating existing rows of data by making in-place column updates where the field value in the update file is different to existing column value.

Full Refresh: This refers to the process of loading a new table, or reloading an existing table in its entirety. When performing a refresh, if a table already exists it will be dropped and reloaded.

Appends

Appends allow Engine administrators to add new records to their databases without having to do a time consuming full refresh. Adding a small amount of data to a large table is more efficient in almost all cases.

Append functionality is optimized for high cardinality, non-memo, partitioned columns. Data with these characteristics will enjoy the highest performance gain. As every customer database is unique, a process of iterative testing and investigation is required to optimize your load strategy; there is no template solution that can be applied to all databases.

Incremental appends have always been possible, but changes included in recent 5.x releases have made them more viable through the improved performance delivered by the 64-bit architecture, and a number of functional enhancements including:

Appends v Full Refresh

It makes sense that it is more efficient to add a small amount of data to a large table than to reload the entire table. We wanted to measure just how much more efficient this was, and try to establish a general rule for when appends would become less efficient than a full data refresh.

Methodology

A full refresh was timed on three different sized databases, with a mix of field cardinality to approximate real customer databases:

• 1 million rows
• 10 million rows
• 1 billion rows

On each database, we have then timed appends of different sizes:

1% > 25% > 66%

Results

As expected, the largest performance gain was achieved on the smallest append (1%).

990 million row load timed at 5h 16m

10 million row append timed at 0h 16m

However, even when doing a 25% append, a greater than 50% performance gain was recorded.

800 million row load timed at 4h 39m

200 million row append timed at 1h 58m

Extrapolating from our final test, the tipping point for our test database where a full refresh would become more efficient than an append would be in the region of 68%.

600 million row load timed at 3h 07m

400 million row append timed at 2h 59m

Updates and In-place Updates

For the Campaign Manager 6.0 release, Alterian have implemented a full In-place update process for all UPDATE processing, meaning the update is made to the data in-situ and an update will never involve the creation of temporary tables and never change the order of the rows in table. This is a much more efficient process and will be used by all UPDATE processing by default.

Rolling Table Updates

The classic scenario where rolling table updates would be useful is where there is a large transaction table with a monthly update cycle to drop the oldest month’s data and add the latest months.

This segmented data enables much more efficient deletion of sections of data.

With partitioned tables, for example using a monthly partition method, if you are loading for example June 2011 and dropping June 2010, the 11 months in-between are all stored in their own partitioned data files.

The advantage of partitioned data files is in the delete speed. Only data files that contain data that needs deleting will be compacted, rather than an un-partitioned data file where all data will be compacted.

Rolling Transaction Update refers to a situation where the following are all true.

1. There is a big table – probably a transaction or order table.
2. New transactions are added to the table periodically. We say monthly, but it could be daily or weekly.
3. Old transactions are dropped periodically once a certain volume or time-span has been reached.

Process

Table partitioning is enabled with the following command:

EnableUpdateTransactions [Demo].[Order], ON

Once enabled, each subsequent append will create a new partition. (It is recommended that the EnableUpdateTransactions command be specified before each append.

Partitioning

Partitioning is a deletion optimization and is only beneficial if the rows are already in the order that they will be deleted. If this is not the case then partitioning should not be used.

PARTITION Command

This command should be used one time when you have decided that the table is suitable.

The partition command groups the rows into separate physical files as identified by the specified row selection domain. The rows are not re-ordered or altered in any way.

Partition [Demo].[Order], {Select Distinct([Demo].[Order].[ALLMONTHS]) from [Demo].[Order]}

Partitioning an existing table using the PARTITION command is only of benefit if you are going to carry out regular rolling deletes. As explained above, the data MUST already be loaded in deletion order i.e. by transaction date and you will be deleting by transaction date.

In Campaign Manager Campaign History data is held in a set of three default Engine tables which are updated continuously through the day while Engine is running. Events relating to campaign activity (Email opens, Links Clicks etc.) are added to these tables and users will be able to report on these tables to get a real-time view of how their campaigns are performing.

The three-table structure consists of:

• Master Campaign Table
• Customer Contact History Table
• Response Contact History Table

Although the three Campaign History tables are system tables, it is possible to create engineered columns using them. Be aware that the data will change throughout the day and take this into account when creating columns that may be used by users in reports or in campaign audience logic. Your users may suddenly ask “Why are there suddenly 100,000 people in my campaign audience – there were only 5,000 this morning?”

Engine 7.0.0 includes new column types and data transformation commands that deal with the requirement to update engineered columns during the day, or create them as static snapshots that will not change until they are recreated during the overnight data load.

I always want my engineered columns to show latest data

If you are running time sensitive campaigns that respond immediately to recipient responses then you will want columns engineered off your Campaign History tables to update with new response data as soon as it is available.

If you fall into this category then you do not need to do anything; the default behavior is for columns to update as soon as the underlying data or a dependent column is updated.

Make users aware which columns only change overnight, and which are based on Campaign History tables that are likely to change during the day.

I don’t want my data to change

There are valid business reasons for creating columns that are snapshots of the data at a point in time. For example, you may create an aggregate column for reporting on Campaign History that is created as part of the overnight data load for a report that is generated and distributed once a day.

The column will be dropped and re-created as part of the load processes tomorrow night, so re-calculating it multiple times during the day serves no purpose and causes unnecessary processing.

In this case, you would use the iLoader Engineer_As_Snapshot command.

ENGINEER_AS_SNAPSHOT TRUE/FALSE

e.g. ENGINEER_AS_SNAPSHOT TRUE

Once set to TRUE, all subsequent data engineering will create snapshot columns.

If this command is not present, engineered columns will be created with SNAPSHOT = False as default.

This DROP_SNAPSHOTS command will drop all snapshot columns on the defined object.

DROP_SNAPSHOTS Full Name of Database or Table

e.g. DROP_SNAPSHOTS [Demo].[Customer]

The following table summarizes behavior when creating engineered columns via iLoader:

iLoaderClient can be run from the command line or shortcut with arguments that affect its behavior.

Arguments can be pre-fixed by hyphen (–) or forward slash (/)

/r

-r

Arguments have one of two forms:

1. Flag - hyphen or forward-slash followed immediately by the name of the flag.
2. NVP (Name value Pair) - similar to flags but identified by the inclusion of a delimiter.

Delimiters can be enclosed in quotes if desired. If no qualifier is present, the argument value is assumed to extend to the start of the next argument or to the end of the command line, whichever comes first.

/s “XXXX”

/s XXXX

NVP delimiters can be specified using a space( ) or a semi-colon (;)

/Project: ProjectName

-Project MyProjectName

Argument names are CASE-insensitive.